﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
using System.IO;

namespace ItemPoolManager
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
#if(DEBUG)
            Directory.SetCurrentDirectory("../Mathgate");
#endif
        }

        private void button_read_Click(object sender, EventArgs e)
        {
            refreshTable();
        }

        private void radioButton_selectable_CheckedChanged(object sender, EventArgs e)
        {
            textBox_option1.Enabled = radioButton_selectable.Checked;
            textBox_option2.Enabled = radioButton_selectable.Checked;
            textBox_option3.Enabled = radioButton_selectable.Checked;
        }

        private void listView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!File.Exists("db.db"))
            {
                MessageBox.Show(this, "데이터베이스 없음!\n경로: " + Directory.GetCurrentDirectory());
                return;
            }

            if (listView1.SelectedIndices.Count == 0)
            {
                empty_textbox();
                return;
            }

            ListViewItem item = listView1.Items[listView1.SelectedIndices[0]];
            SQLiteConnection conn = new SQLiteConnection("Data Source=db.db;Version=3;New=True;Compress=True;");
            conn.Open();

            SQLiteCommand sql = conn.CreateCommand();
            sql.CommandText = "select * from ItemPool where id=" + item.Text;

            SQLiteDataReader data = sql.ExecuteReader();
            while (data.Read())
            {
                textBox_id.Text = data["id"].ToString();
                textBox_question.Text = data["question"].ToString();
                textBox_answer.Text = data["answer"].ToString();
                comboBox_level.SelectedIndex = int.Parse(data["level"].ToString());
                comboBox_course.SelectedIndex = int.Parse(data["course"].ToString());

                if (data["option1"].Equals(DBNull.Value))
                {
                    radioButton_nonselectable.PerformClick();
                    textBox_option1.Text = "";
                    textBox_option2.Text = "";
                    textBox_option3.Text = "";
                }
                else
                {
                    radioButton_selectable.PerformClick();
                    textBox_option1.Text = data["option1"].ToString();
                    textBox_option2.Text = data["option2"].ToString();
                    textBox_option3.Text = data["option3"].ToString();
                }
            }
        }

        private void refreshTable()
        {
            listView1.Items.Clear();
            if (!File.Exists("db.db"))
            {
                MessageBox.Show(this, "데이터베이스 없음!\n경로: " + Directory.GetCurrentDirectory());
                return;
            }
            SQLiteConnection conn = new SQLiteConnection("Data Source=db.db;Version=3;New=False;Compress=True;");
            conn.Open();

            SQLiteCommand sql = conn.CreateCommand();
            sql.CommandText = "select * from ItemPool";

            SQLiteDataReader data = sql.ExecuteReader();
            while (data.Read())
            {
                ListViewItem item = new ListViewItem(data["id"].ToString());
                item.SubItems.Add(data["level"].ToString());
                item.SubItems.Add(data["course"].ToString());
                item.SubItems.Add(data["question"].ToString());
                item.SubItems.Add(data["answer"].ToString());
                listView1.Items.Add(item);
            }

            data.Dispose();
            sql.Dispose();
            conn.Dispose();
            conn.Close();
            empty_textbox();
        }

        private void empty_textbox()
        {
            textBox_id.Text = "";
            textBox_answer.Text = "";
            textBox_option1.Text = "";
            textBox_option2.Text = "";
            textBox_option3.Text = "";
            textBox_question.Text = "";

            comboBox_course.SelectedIndex = 0;
            comboBox_level.SelectedIndex = 0;
        }

        private bool check_form()
        {
            if (textBox_question.Text == "")
                MessageBox.Show("질문을 입력하세요");
            else if (textBox_answer.Text == "")
                MessageBox.Show("답을 입력하세요");
            else if (radioButton_selectable.Checked &&
                (textBox_option1.Text == "" ||
                textBox_option2.Text == "" ||
                textBox_option3.Text == ""))
                MessageBox.Show("옵션을 모두 입력하세요");
            else if (comboBox_course.SelectedIndex < 0)
                MessageBox.Show("과목을 선택하세요");
            else if (comboBox_level.SelectedIndex < 0)
                MessageBox.Show("난이도를 선택하세요");
            else
                return true;

            return false;
        }

        private void button_submit_Click(object sender, EventArgs e)
        {
            // check
            if (!check_form())
                return;

            if (!File.Exists("db.db"))
            {
                MessageBox.Show(this, "데이터베이스 없음!\n경로: " + Directory.GetCurrentDirectory());
                return;
            }
            SQLiteConnection conn = new SQLiteConnection("Data Source=db.db;Version=3;New=True;Compress=True;");
            conn.Open();

            SQLiteCommand sql = conn.CreateCommand();
            if (radioButton_selectable.Checked)
            {
                sql.CommandText = "insert into ItemPool (`ID`, `level`, `course`, `question`, `answer`, `option1`, `option2`, `option3`) " +
                                    "values ((select max(`id`)+1 from ItemPool), " +
                                    comboBox_level.SelectedIndex + ", " +
                                    comboBox_course.SelectedIndex + ", " +
                                    makeString(textBox_question.Text) + ", " +
                                    makeString(textBox_answer.Text) + ", " +
                                    makeString(textBox_option1.Text) + ", " +
                                    makeString(textBox_option2.Text) + ", " +
                                    makeString(textBox_option3.Text) +
                                    ");";
            }
            else
            {
                sql.CommandText = "insert into ItemPool (`ID`, `level`, `course`, `question`, `answer`, `option1`, `option2`, `option3`) " +
                                    "values ((select max(`id`)+1 from ItemPool), " +
                                    comboBox_level.SelectedIndex + ", " +
                                    comboBox_course.SelectedIndex + ", " +
                                    makeString(textBox_question.Text) + ", " +
                                    makeString(textBox_answer.Text) + ", " +
                                    "null, null, null" +
                                    ");";
            }

            if (sql.ExecuteNonQuery() != 1)
                MessageBox.Show("DB 에러");

            sql.Dispose();
            conn.Dispose();
            conn.Close();

            refreshTable();
        }

        private void button_delete_Click(object sender, EventArgs e)
        {
            if (textBox_id.Text == "")
            {
                MessageBox.Show("삭제할 문제를 선택하세요");
                return;
            }

            if (!File.Exists("db.db"))
            {
                MessageBox.Show(this, "데이터베이스 없음!\n경로: " + Directory.GetCurrentDirectory());
                return;
            }
            SQLiteConnection conn = new SQLiteConnection("Data Source=db.db;Version=3;New=True;Compress=True;");
            conn.Open();

            SQLiteCommand sql = conn.CreateCommand();
            sql.CommandText = "delete from ItemPool where id=" + textBox_id.Text;

            if (sql.ExecuteNonQuery() != 1)
                MessageBox.Show("DB 에러");

            sql.Dispose();
            conn.Dispose();
            conn.Close();

            refreshTable();
        }

        private String makeString(String original)
        {
            String ret = original.Replace("'", "''");
            return "'" + ret + "'";
        }

        private void button_modify_Click(object sender, EventArgs e)
        {
            // check
            if (!check_form())
                return;

            if (!File.Exists("db.db"))
            {
                MessageBox.Show(this, "데이터베이스 없음!\n경로: " + Directory.GetCurrentDirectory());
                return;
            }
            SQLiteConnection conn = new SQLiteConnection("Data Source=db.db;Version=3;New=True;Compress=True;");
            conn.Open();

            SQLiteCommand sql = conn.CreateCommand();
            if (radioButton_selectable.Checked)
            {
                sql.CommandText = "update ItemPool set " +
                                    "level=" + comboBox_level.SelectedIndex + ", " +
                                    "course=" + comboBox_course.SelectedIndex + ", " +
                                    "question=" + makeString(textBox_question.Text) + ", " +
                                    "answer=" + makeString(textBox_answer.Text) + ", " +
                                    "option1=" + makeString(textBox_option1.Text) + ", " +
                                    "option2=" + makeString(textBox_option2.Text) + ", " +
                                    "option3=" + makeString(textBox_option3.Text) + " " +
                                    "where id=" + textBox_id.Text +
                                    ";";
            }
            else
            {
                sql.CommandText = "update ItemPool set " +
                                    "level=" + comboBox_level.SelectedIndex + ", " +
                                    "course=" + comboBox_course.SelectedIndex + ", " +
                                    "question=" + makeString(textBox_question.Text) + ", " +
                                    "answer=" + makeString(textBox_answer.Text) + ", " +
                                    "option1=null, option2=null, option3=null" + " " +
                                    "where id=" + textBox_id.Text +
                                    ";";
            }

            if (sql.ExecuteNonQuery() != 1)
                MessageBox.Show("DB 에러");

            sql.Dispose();
            conn.Dispose();
            conn.Close();

            refreshTable();
        }
    }
}
